/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ContentManagers.RoleManagers;

import ContentManagers.Security.Authenticator;
import ContentManagers.Models.ProductManager;
import ContentManagers.Models.Administrator;
import ContentManagers.Models.AccountingManager;
import ContentManagers.Security.Validator;
import DBConnection.ConnectionFactory;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Calendar;

/**
 *
 * @author Ervin James
 */
public class AdminManager {
    
    public ResultSet getManagerTypes()
    {
              try{

                ConnectionFactory myFactory = ConnectionFactory.getFactory("admin");
                Connection conn = myFactory.getConnection();

                int i = 1;

                PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM product_type");

                ResultSet rs = pstmt.executeQuery();

                return rs;
                }
                catch(SQLException e)
                {
                e.printStackTrace();
                return null;
                }
    }
    
    public boolean addAdmin(Administrator admin){
        boolean result = false;

         try{
            //opens db connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory("admin");
            Connection conn = myFactory.getConnection();

            int i = 1;

            //SQL Query
            PreparedStatement pstmt = conn.prepareStatement("INSERT INTO administrator (username,password,password_salt) values (?,?,?)");
            //include parameters
         
            
            pstmt.setString(i++, admin.getUsername());
            pstmt.setString(i++, admin.getPassword());
            pstmt.setString(i++, admin.getPasswordSalt());
            
            
            //other fields here

            //execute query
            pstmt.executeUpdate();

            //close DB connection
            conn.close();

            result=true;
            }
            catch(SQLException e)
            {
               e.printStackTrace();
            }
            return result;
    }
    
     public boolean authenticateAdmin (Administrator admin)
    {
        
        boolean result = false;

        try{
            ConnectionFactory myFactory = ConnectionFactory.getFactory("admin");
            Connection conn = myFactory.getConnection();

            int i = 1;

            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM administrator WHERE username = ?");
          
            pstmt.setString(i++, admin.getUsername());
                     
            ResultSet rs = pstmt.executeQuery();
            
            while(rs.next())
            {             
                if(Authenticator.CompareHash(admin.getPassword(), rs.getString("password"), rs.getString("password_salt")))
                    result = true; 
            }
            
            
            conn.close();
        }
        catch(SQLException e)
        {
           e.printStackTrace();
        }

        return result;  
    }
         public boolean checkProductManager(ProductManager pm)
       {
           boolean result = false;

        try {
            ConnectionFactory myFactory = ConnectionFactory.getFactory("admin");
            Connection conn = myFactory.getConnection();
          
            int i = 1;

            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM product_manager where username = ?");
            pstmt.setString(i++, pm.getUsername());
            
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {         
                result = true;
            }
            conn.close();
            
       }  catch (SQLException ex) {
            ex.printStackTrace();
        }


        return result;
     }
     public boolean addProductManager(ProductManager pm)
    {
        boolean result = false;

         try{
            //opens db connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory("admin");
            Connection conn = myFactory.getConnection();
            
            int i = 1;
            
            //SQL Query
            PreparedStatement pstmt = conn.prepareStatement("INSERT INTO product_manager (username,password,manager_type_id,password_salt) VALUES(?,?,?,?)");
                   
            //include parameters
            pstmt.setString(i++, pm.getUsername());
            pstmt.setString(i++, pm.getPassword());
            pstmt.setInt(i++, pm.getMTid());
            pstmt.setString(i++, pm.getPasswordSalt());
            

            //execute query
            pstmt.executeUpdate();


            //close DB connection
            conn.close();

            result=true;
            }
            catch(SQLException e)
            {
               e.printStackTrace();
            }
            return result;
    }
     
     public boolean checkAccountingManager(AccountingManager am)
     {
        boolean result = false;

        try {
            ConnectionFactory myFactory = ConnectionFactory.getFactory("admin");
            Connection conn = myFactory.getConnection();
          
            int i = 1;

            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM accounting_manager where username = ?");
            pstmt.setString(i++, am.getUsername());
            
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {         
                result = true;
            }
            conn.close();
            
       }  catch (SQLException ex) {
            ex.printStackTrace();
        }


        return result;
     }
      public boolean addAccountingManager(AccountingManager am)
    {
        boolean result = false;

         try{
            //opens db connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory("admin");
            Connection conn = myFactory.getConnection();

            int i = 1;

            //SQL Query
            PreparedStatement pstmt = conn.prepareStatement("INSERT INTO accounting_manager (username,password,password_salt) VALUES(?,?,?)");

            //INSERT HASHING HERE
            
            //include parameters   
            pstmt.setString(i++, am.getUsername());
            pstmt.setString(i++, am.getPassword());
            pstmt.setString(i++, am.getPasswordSalt());
            

            //execute query
            pstmt.executeUpdate();


            //close DB connection
            conn.close();

            result=true;
            }
            catch(SQLException e)
            {
               e.printStackTrace();
            }
            return result;
    }
      
    public ResultSet getLockedPMAccounts()
    {
        try{
            ConnectionFactory myFactory = ConnectionFactory.getFactory("admin");
            Connection conn = myFactory.getConnection();

            //Construct SQL Query
            PreparedStatement pstmt;
            
            pstmt = conn.prepareStatement("SELECT * from product_manager WHERE HOUR(TIMEDIFF(CURRENT_TIMESTAMP(),password_timestamp)) > 24");
            
            //execute SQL statement
            ResultSet result = pstmt.executeQuery();
            
            
            return result;

            }
         catch (SQLException ex) {ex.printStackTrace();return null;}
    }

    public boolean renewPMPassword(ProductManager pm)
    {
        boolean result = false;

         try{
            //opens db connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory("admin");
            Connection conn = myFactory.getConnection();
            
            int i = 1;
            
            //SQL Query
            PreparedStatement pstmt = conn.prepareStatement("UPDATE product_manager set password = ?, password_salt = ?, password_timestamp = ? WHERE product_manager_id = ?");
                   
            //include parameters
            pstmt.setString(i++, pm.getPassword());
            pstmt.setString(i++, pm.getPasswordSalt());
            Calendar date = Calendar.getInstance();
            SimpleDateFormat dateformatter = new SimpleDateFormat("yyyy-MM-dd kk:mm:ss");
            pstmt.setString(i++, dateformatter.format(date.getTime()));
            pstmt.setInt(i++, pm.getPMid());
            

            //execute query
            pstmt.executeUpdate();


            //close DB connection
            conn.close();

            result=true;
            }
            catch(SQLException e)
            {
               e.printStackTrace();
            }
            return result;
    }
    
    public ResultSet getLockedAMAccounts()
    {
        try{
            ConnectionFactory myFactory = ConnectionFactory.getFactory("admin");
            Connection conn = myFactory.getConnection();

            //Construct SQL Query
            PreparedStatement pstmt;
            
            pstmt = conn.prepareStatement("SELECT * from accounting_manager WHERE HOUR(TIMEDIFF(CURRENT_TIMESTAMP(),password_timestamp)) > 24");
            
            //execute SQL statement
            ResultSet result = pstmt.executeQuery();
            
            
            return result;

            }
         catch (SQLException ex) {ex.printStackTrace();return null;}
    }
    
    public boolean renewAMPassword(AccountingManager am)
    {
        boolean result = false;

         try{
            //opens db connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory("admin");
            Connection conn = myFactory.getConnection();
            
            int i = 1;
            
            //SQL Query
            PreparedStatement pstmt = conn.prepareStatement("UPDATE accounting_manager set password = ?, password_salt = ?, password_timestamp = ? WHERE accounting_manager_id = ?");
                   
            //include parameters
            pstmt.setString(i++, am.getPassword());
            pstmt.setString(i++, am.getPasswordSalt());
            Calendar date = Calendar.getInstance();
            SimpleDateFormat dateformatter = new SimpleDateFormat("yyyy-MM-dd kk:mm:ss");
            pstmt.setString(i++, dateformatter.format(date.getTime()));
            pstmt.setInt(i++, am.getId());
            

            //execute query
            pstmt.executeUpdate();


            //close DB connection
            conn.close();

            result=true;
            }
            catch(SQLException e)
            {
               e.printStackTrace();
            }
            return result;
    }
    
    public ResultSet getDisabledPM()
    {
         try{
            ConnectionFactory myFactory = ConnectionFactory.getFactory("admin");
            Connection conn = myFactory.getConnection();

            //Construct SQL Query
            PreparedStatement pstmt;
            
            pstmt = conn.prepareStatement("SELECT * from product_manager WHERE account_status = 0");
            
            //execute SQL statement
            ResultSet result = pstmt.executeQuery();
            
            
            return result;

            }
         catch (SQLException ex) {ex.printStackTrace();return null;}
    }
    
    public boolean unlockPM(int id)
    {
         boolean result = false;

         try{
            //opens db connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory("admin");
            Connection conn = myFactory.getConnection();
            
            int i = 1;
            
            //SQL Query
            PreparedStatement pstmt = conn.prepareStatement("UPDATE product_manager set account_status = 1 WHERE product_manager_id = ?");
                   
            //include parameters
            pstmt.setInt(i++, id);
            

            //execute query
            pstmt.executeUpdate();


            //close DB connection
            conn.close();

            result=true;
            }
            catch(SQLException e)
            {
               e.printStackTrace();
            }
            return result;
    }
    
       public ResultSet getDisabledAM()
    {
         try{
            ConnectionFactory myFactory = ConnectionFactory.getFactory("admin");
            Connection conn = myFactory.getConnection();

            //Construct SQL Query
            PreparedStatement pstmt;
            
            pstmt = conn.prepareStatement("SELECT * from accounting_manager WHERE account_status = 0");
            
            //execute SQL statement
            ResultSet result = pstmt.executeQuery();
            
            
            return result;

            }
         catch (SQLException ex) {ex.printStackTrace();return null;}
    }
    
     public boolean unlockAM(int id)
    {
         boolean result = false;

         try{
            //opens db connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory("admin");
            Connection conn = myFactory.getConnection();
            
            int i = 1;
            
            //SQL Query
            PreparedStatement pstmt = conn.prepareStatement("UPDATE accounting_manager set account_status = 1 WHERE accounting_manager_id = ?");
                   
            //include parameters
            pstmt.setInt(i++, id);
            

            //execute query
            pstmt.executeUpdate();


            //close DB connection
            conn.close();

            result=true;
            }
            catch(SQLException e)
            {
               e.printStackTrace();
            }
            return result;
    }
     
    public ResultSet getDisabledCustomer()
    {
         try{
            ConnectionFactory myFactory = ConnectionFactory.getFactory("admin");
            Connection conn = myFactory.getConnection();

            //Construct SQL Query
            PreparedStatement pstmt;
            
            pstmt = conn.prepareStatement("SELECT * from customer WHERE account_status = 0");
            
            //execute SQL statement
            ResultSet result = pstmt.executeQuery();
            
            
            return result;

            }
         catch (SQLException ex) {ex.printStackTrace();return null;}
    }
    
    public boolean unlockCustomer(int id)
    {
         boolean result = false;

         try{
            //opens db connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory("admin");
            Connection conn = myFactory.getConnection();
            
            int i = 1;
            
            //SQL Query
            PreparedStatement pstmt = conn.prepareStatement("UPDATE customer set account_status = 1 WHERE customer_id = ?");
                   
            //include parameters
            pstmt.setInt(i++, id);
            

            //execute query
            pstmt.executeUpdate();


            //close DB connection
            conn.close();

            result=true;
            }
            catch(SQLException e)
            {
               e.printStackTrace();
            }
            return result;
    }
    
    
}
